package com.example.demo.repository;

import com.example.demo.entity.Users;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository <Users,Integer>{
    @Query("select t from Users t where t.username=:username and t.password=:password and t._live=:_live and t.type=:type")
    Users login(@Param("username") String username,@Param("password") String password,@Param("_live") Integer _live,@Param("type")String type);

    @Query("select t from Users t where t.id=:id and t._live=:_live")
    Users findBy_live(@Param("id") Integer id,@Param("_live") Integer _live);

    @Query("select t from Users t where t.username=:username  and t._live=:_live")
    Users findByName(@Param("username") String username,@Param("_live") Integer _live);

    @Query(value = "select t.id,t.username,t._live,t.mobile,t.email,t.password,t.type from Users t where t._live=1 and t.type=:type order by t.id desc limit :page,:pageSize",nativeQuery = true)
    List<Users> findAllUser(@Param("page")Integer page,@Param("pageSize") Integer pageSize,@Param("type")String type);

    @Query(value = "select count(u.id) from (select t.id,t.username,t._live,t.mobile,t.email,t.password,t.type from Users t where t._live=1 and t.type=:type)as u",nativeQuery = true)
    int totals(@Param("type")String type);

    @Query(value = "select t.id,t.username,t._live,t.mobile,t.email,t.password,t.type from Users t where t._live=1  order by t.id desc limit :page,:pageSize",nativeQuery = true)
    List<Users> findByAny(@Param("page")Integer page,@Param("pageSize") Integer pageSize);

    @Query(value = "select count(u.id) from(select t.id,t.username,t._live,t.mobile,t.email,t.password,t.type from Users t where t._live=1)as u",nativeQuery = true)
    int total();
}
